<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Illuminate\Support\Facades\Storage;

class ExportReportController extends Controller
{
    /* 导出Excel 检测报告*/
    public function export(Request $request) {
        if($request->sample){
            return $this->exportSample();
        }
        $app_name = config('constant.app_name');
        $className = "App\\Http\\Controllers\\Admin\\ExportReport\\ExportReport{$app_name}Controller";

        if (!class_exists($className)) {
            $className = "App\\Http\\Controllers\\Admin\\ExportReport\\ExportReportPublicController";
        }
        $exportReport = new $className();
        return $exportReport->export($request);
    }

    /* 导出批次原始记录*/
    public function origin(Request $request) {
        $app_name = config('constant.app_name');
        // $app_name = "HuiBangJianCe";
        $className = "App\\Http\\Controllers\\Admin\\ExportOrigin\\{$app_name}Controller";
        if (!class_exists($className)) {
            $className = "App\\Http\\Controllers\\Admin\\ExportOrigin\\PublicController";
        }
        // dd($className);
        $exportReport = new $className();
        return $exportReport->export($request);
    }

    /*导出单个样品检测报告*/
    private function exportSample(){
        $temp_FM = './storage/ExcelTemplate/单样品封面.xls'; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($temp_FM)) {
            return '单样品封面模板文件不存在';
        }

        //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
        $report_num_pre = DB::table('setting')->where('id', '1')->value('report_num_pre'); //报告编号前缀
        $company_name = DB::table('setting')->where('id', '1')->value('name_rep'); //获取检测公司名称
        $id = request('id');
        $info = DB::table('a_sample_info as a')
                ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
                ->leftJoin('a_contract as c','a.contract_id','=','c.id')
                ->leftJoin('a_status_time as d','d.contract_id','=','c.id')
                ->where('a.id',$id)
                ->select('a.id','c.entrusted_company','b.tool_name','b.voltage','c.test_kind','a.manufacturer','a.self_num','a.test_time','b.std_num','a.res','a.remark','a.tester_names','a.audit_man','c.audit_man as pi_man','d.sendback_end','b.std_id','b.tool_id','c.created_at as c_create','b.class','b.class_id','b.vol_id','b.item_ids','a.bar_code','c.batchNum')
                ->first();
        // dd($info);
        if (!$info) {
            return '样品不存在！';
        }
        // dd($info);
        $info_pos = DB::table('a_excel_pos as B')
            ->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
            ->where(['A.std_id' => $info->std_id, 'A.tool_id' => $info->tool_id, 'B.is_default' => 1])
            ->select('B.*', 'A.cycle', 'A.ctime', 'A.remark')->first();
        // dd($info_pos);
        if (!$info_pos) {
            return "{$info->tool_name} 未指定数据在模板文件中的位置";
        }
        $posname = $info_pos->name === '默认' ? '' : $info_pos->name;
        # 检测模版文件是否存在
        $temp_tool = "./storage/ExcelTemplate/{$info->std_id}/{$info->tool_name}{$posname}.xls"; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($temp_tool)) {
            return "{$info->tool_name}的模板文件不存在";
        }
        /***********************************************样品  start***************************************************/
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp_tool); //读取模板文件
        $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
        $worksheet->getHeaderFooter()->setOddHeader('')->setEvenHeader('')->setOddFooter('')->setEvenFooter('');
        $worksheet->setTitle($info->tool_name.$info->std_id);
        //未指定行号
        if ($info_pos->line_num === '' || intval($info_pos->line_num) < 5) {
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            return "{$v->tool_name} 未指定数据在模板文件中的行号";
        }

        $DY_RiQi = strtotime($info->c_create); //将表示时间和日期的字符串转化为相应的时间戳
        if ($info_pos->come_date !== '') {
            $comeDateCell = $info_pos->come_date;
            $worksheet->getCell($comeDateCell)->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期
        }

        //------------------------------------------检测日期---------
        if ($info_pos->test_date !== ''){
            $testDateCell = $info_pos->test_date;
            $worksheet->getCell($testDateCell)->setValue($info->test_time); //检测日期
        }
        //-------------------------------------------------------------------
        if ($info_pos->sample_count !== '') {
            $worksheet->getCell($info_pos->sample_count)->setValue(1); //样品数量
        }
        $temp_hum_pressure_data = DB::table('a_item_data as B')
                            ->select('B.temp', 'B.hum', 'B.pressure')
                            ->leftJoin('a_sample_info as A', 'A.id', '=', 'B.sample_info_id')
                            ->where('B.sample_info_id', $id)
                            ->where('B.item_id', '!=', 61)
                            ->get();
        // dd($temp_hum_pressure_data);
        $ExpPub = new \App\Http\Controllers\Admin\ExportReport\ExportReportPublicController();
        $se = $ExpPub::tempHumPressureSE($temp_hum_pressure_data);
        [$temp, $hum, $pressure] = $se;
        // dd($se);
        //------------------------------------------ 温度 start---------
        if ($info_pos->temp !== '') {
            $worksheet->getCell($info_pos->temp)->setValue($temp); //温度
        }
        //------------------------------------------ 温度 end ---------
        //------------------------------------------ 相对湿度 start---------
        if ($info_pos->hum !== '') {
            $worksheet->getCell($info_pos->hum)->setValue($hum); //相对湿度
        }
        //------------------------------------------ 相对湿度 end ---------
        //------------------------------------------ 大气压力 start---------
        if ($info_pos->pressure !== '') {
            $worksheet->getCell($info_pos->pressure)->setValue($pressure); //大气压力
        }
        //------------------------------------------ 大气压力 end ---------
        //------------------------------------------ 天气 start---------
        $wea = DB::table('a_item_data as B')->leftJoin('a_sample_info as A', 'A.id', '=', 'B.sample_info_id')->where('A.id', $id)->where('wea', '!=', '')->value('wea');
        // dd($wea);
        if ($info_pos->wea !== '' && $wea) {
            $worksheet->getCell($info_pos->wea)->setValue($wea);
        }
        //------------------------------------------ 天气 end ---------

        $rowIndex = $initRowIndex = $info_pos->line_num;
        $sampleCount = 1;
        if ($info_pos->serial_num1 !== '') {
            $worksheet->getCell($info_pos->serial_num1 . $rowIndex)->setValue(1); //序号
        }
        if ($info_pos->sample_num1 !== '') {
            $worksheet->getCell($info_pos->sample_num1 . $rowIndex)->setValue(substr($info->bar_code, 6) . '#'); //样品号
        }
        if ($info_pos->self_num1 !== '') {
            $worksheet->getCell($info_pos->self_num1 . $rowIndex)->setValue($info->self_num); //自编号
        }
        if ($info_pos->voltage1 !== '') {
            $voltageWithoutKV = str_replace("kV", "", $info->voltage); //将额定电压中的 kV去掉
            $worksheet->getCell($info_pos->voltage1 . $rowIndex)->setValue($voltageWithoutKV); //额定电压
        }
        if ($info_pos->class1 !== '') {
            $worksheet->getCell($info_pos->class1 . $rowIndex)->setValue($info->class); //分类
        }
        $panding = '';
        if ($info->res === 1) {
            $panding = '合格';
        }
        elseif ($info->res === 2) {
            $panding = '不合格';
        }
        if ($info_pos->res1 !== '') {
            $worksheet->getCell($info_pos->res1 . $rowIndex)->setValue($panding); //判定
        }
        //有效日期有位置 而且 周期不为0或null
        if ($info_pos->valid_date1 !== '' && $info_pos->cycle) {
            if ($info->res === 1) {
                //合格
                $test_time = $info->test_time; //试验日期
                $cycle = ($info_pos->cycle) * 12; //周期

                $valid_date = date("Y-m-d", strtotime("+$cycle month", strtotime($test_time)));
                $first_date = date("Y-m-d", strtotime("first day of +$cycle month", strtotime($test_time)));
                if (substr($valid_date, 0, 7) !== substr($first_date, 0, 7)) {
                    $valid_date = date("Y-m-d", strtotime("last day of +$cycle month", strtotime($test_time)));
                }

                $valid_date = date('Y-m-d', strtotime("-1 day", strtotime($valid_date)));
            }
            else {
                # 不合格
                $valid_date = "—";
            }
            $worksheet->getCell($info_pos->valid_date1 . $rowIndex)->setValue($valid_date); //有效日期
        }
        //------------------------------------------ 试验条件 start ---------
        $key_condition = DB::table('a_condition_key')->select('id', 'item_id', 'pos_start', 'pos_end')->where([['std_id', '=', $info->std_id], ['tool_id', '=', $info->tool_id], ['pos_start', '!=', ''], ['pos_end', '!=', '']])->get();
        foreach ($key_condition as $vvv) {
            $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
            $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
            if ($b) {
                # code...
                $val_condition = DB::table('a_condition')->where('condition_key_id', '=', $vvv->id)->whereIn('vol_class_id', [0, $info->vol_id, $info->class_id])->value('val');
            }
            else {
                # code...
                $val_condition = '/';
            }
            $vvvstart = $vvv->pos_start;
            $vvvend = $vvv->pos_end;

            if ($vvvstart && $vvvend) {
                $worksheet->mergeCells($vvvstart . $rowIndex . ':' . $vvvend . $rowIndex); //试验条件  合并单元格
                $worksheet->getCell($vvvstart . $rowIndex)->setValue($val_condition); //试验条件
            }
        } //内内层 foreach 结尾
        // dd($key_condition);
        //------------------------------------------ 试验条件 end ---------

        //------------------------------------------ 单项目的检测结果（符合、不符合） start ---------
        $item_res = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id', 'pos_start', 'pos_end')->where([['std_id', '=', $info->std_id], ['tool_id', '=', $info->tool_id]])->get();
        $resDef = '';
        foreach ($item_res as $k => $vvv) {
            $vvvstart = $vvv->pos_start;
            $vvvend = $vvv->pos_end;
            if ($vvvstart && $vvvend) {
                if ($k === 0) {
                    $resDef = $worksheet->getCell($vvvstart.$initRowIndex)->getValue();
                }
                $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
                $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                if ($b) {
                    $test_item = DB::table('a_item_data')->where([
                            ['sample_info_id', '=', $info->id],
                            ['item_id', '=', $vvv->testitem_id]
                        ])->select('remark','res')->first();
                    $test_res = $test_item->res;
                    //结论,0-不符合，1-符合，2-未录入
                    if($test_res === 0) {
                        $remark = $test_item->remark;
                        $test_res = $remark == "" || $remark == "无" ? '不符合' : $remark;
                    }
                    elseif($test_res === 1) {
                        $test_res = $resDef ?: '符合';
                    }
                    elseif ($test_res === 3) {
                        $test_res = '/';
                    }
                    else {
                        $test_res = '';
                    }
                }
                else {
                    $test_res = '/';
                }
                $worksheet->getCell($vvvstart . $rowIndex)->setValue($test_res); //单项目的检测结果
            }
            $key_data = DB::table('a_testdata_key')
                ->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id','line_num')
                ->where('std_tool_item_rel_id',$vvv->id)
                ->where([
                    ['pos_start', '!=', ''],
                    ['pos_end', '!=', '']
                ])
                ->get();
            //------------------------------------------ 试验数据 start ---------
            foreach ($key_data as $kdKey => $vvv2) {
                $arr_item_id = explode(',', $info->item_ids); //字符串 --》数组
                $b = in_array($vvv2->item_id, $arr_item_id); //in_array($value, $array);
                if ($b) {
                    # code...
                    $val_testdata = DB::table('a_input_data')->where([['sample_info_id', '=', $info->id], ['testdata_key_id', '=', $vvv2->id]])->value('val');
                    // dd( "{$val_testdata} --- {$info->id}---- {$vvv2->id}");
                } else {
                    # code...
                    $val_testdata = '/';
                }

                try {
                    $vvv2start = $vvv2->pos_start;
                    $vvv2end = $vvv2->pos_end;
                    $vvv2LineNum = $vvv2->line_num;
                }catch(\Exception $exception){
                    $vvv2start = $vvv2end = $vvv2LineNum = '';
                }
                if ($vvv2start && $vvv2end && $val_testdata) {
                    $rowIndexItemData = $vvv2LineNum ? $rowIndex+($vvv2LineNum-$initRowIndex) : $rowIndex;
                    $worksheet->getCell($vvv2start . $rowIndexItemData)->setValue(' '.$val_testdata.' '); //试验数据
                }
            }
            //------------------------------------------ 试验数据 end ---------
        } //内内层 foreach 结尾
        //------------------------------------------ 单项目的检测结果（符合、不符合） end   ---------

        /***********************************************样品  end***************************************************/
        //检测结论，检验结论
        $rowIndex += 1;
        $jlcellstr = trim($worksheet->getCell('A' . $rowIndex)->getValue());
        if ($jlcellstr === '检测结论' || $jlcellstr === '检验结论') {
            $range = $worksheet->getCell('A' . $rowIndex)->getMergeRange();
            $rangeAry = explode(':', $range);
            $ABCpos = dec2ABC(ABC2decimal(substr($rangeAry[1], 0, 1)) + 1);
            $worksheet->getCell($ABCpos . $rowIndex)->setValue($panding);
        }
        // dd($item_res);
        //添加封面(写在样品模板前面可能会引起模板样式改变)
        $setting = DB::table('setting')->find(1);
        // /***********************************************封面1  start***************************************************/
        $spreadsheet2 = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp_FM); //读取模板文件
        $worksheet = $spreadsheet2->getSheetByName('封面');
        $worksheet->getHeaderFooter()->setOddHeader('')->setEvenHeader('')->setOddFooter('')->setEvenFooter('');
        $worksheet->setCellValue('P25',$info->entrusted_company);
        $worksheet->setCellValue('P26',$info->tool_name);
        $worksheet->setCellValue('P27',$info->voltage);
        $worksheet->setCellValue('P28',$info->test_kind);
        $worksheet->setCellValue('A44',$setting->name_rep);
        $spreadsheet->addExternalSheet($worksheet,0); //添加外部工作表
        // /***********************************************封面1  end***************************************************/
        // /***********************************************封面2  start***************************************************/
        $worksheet = $spreadsheet2->getSheetByName('封面2');
        $worksheet->getHeaderFooter()->setOddHeader('')->setEvenHeader('')->setOddFooter('')->setEvenFooter('');
        $worksheet->setCellValue('E1',$info->entrusted_company);
        $worksheet->setCellValue('N1',$info->manufacturer);
        $worksheet->setCellValue('E4',$info->tool_name);
        $worksheet->setCellValue('N4',$info->voltage);
        $worksheet->setCellValue('N7',$info->self_num);
        $worksheet->setCellValue('E10',$info->test_kind);
        $worksheet->setCellValue('N10',$info->test_time);
        $worksheet->setCellValue('E13',$info->std_num);
        $worksheet->setCellValue('E21',$info->res == 1 ? '合格' : ($info->res == 2 ? '不合格' : ''));
        $worksheet->setCellValue('E32',$info->remark);
        $worksheet->setCellValue('E35',$info->tester_names);
        // $worksheet->setCellValue('N38',$info->audit_man);
        // $worksheet->setCellValue('E41',$info->pi_man);
        // $worksheet->setCellValue('N41',$info->sendback_end);
        $spreadsheet->addExternalSheet($worksheet,1); //添加外部工作表
        /***********************************************封面2  end***************************************************/
        //将页面定位到首页
        $spreadsheet->setActiveSheetIndex(0);

        //----------直接在浏览器下载
        $file_name = "检测报告{$info->tool_name}{$info->self_num}.xls"; //输出的文件名称
        $storeFile = request('storeFile','');
        return self::saveDown($spreadsheet,$file_name,$storeFile);
    }

    public static function saveDown($spreadsheet,$file_name,$storeFile){
        //----------直接在浏览器下载----start
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        if ($storeFile) {
            $disk = Storage::disk('public');
            $disk->put($storeFile,''); //创建路径
            $path = $disk->path($storeFile);
            $writer->save($path);
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            return $path;
        }

        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename=' . urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end

        // $writer->save('write.xls');//将Excel文件保存在服务器的磁盘上
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $writer->save('php://output'); //直接在浏览器下载

        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

}
